<?php
/*
 * Created on 2016-8-12
 * By lvchengfeng
 */
header('Content-type: text/html; charset=UTF8');
require_once 'PHPExcel/PHPExcel.php';
require_once 'PHPExcel/PHPExcel/IOFactory.php';
require_once 'PHPExcel/PHPExcel/Reader/Excel5.php';
error_reporting(E_ALL);
date_default_timezone_set('PRC');

//常量数组
$title_cov_arr = array(
		'id'=>'序号',
		'username'=>'用户名',
		'description'=>'反馈内容',
		'product_type'=>'产品方向',
		'mobile_os_version'=>'系统版本',
		'mobile_net_type'=>'网络类型',
		'mobile_os_name'=>'系统',
		'mobile_app_name'=>'App包名',
		'mobile_app_version'=>'App版本',
		'extend_appid'=>'SDK编号',
		'extend_sdk_id'=>'SDK消息',
		'extend_pccode'=>'百度客户端通用设备唯一标识',
		'mobile_model'=>'手机型号',
		'mobile_screen_size'=>'屏幕',
		'ip'=>'IP地址',
		'submit_time'=>'提交时间',
		'feedback_type'=>'反馈类型'
);  

/**数据获取**/
$url = 'http://ufo.baidu.com/listen/api/feedbacklist';
//自动获取前一天的时间，0点到24点
$startTime = date('Y-m-d ',strtotime('-1 day')).'00:00:00' ;  
$endTime = date('Y-m-d ',strtotime('-1 day')).'23:59:59';
//备注说明，由于接口必须要numPerPage，page参数
$data = array(  
    'product_line'=>1012,  
    'third_name'=>'browser',  
    'token'=>'1c9d8213f014b5224fd50a38c28b4af8',
	'startTime'=>$startTime,
	'endTime'=>$endTime,
	'numPerPage'=>40,
	'page'=>1
);  //关联数组
 
$json_data = postData($url, $data);   //json_data数据格式
$ufo_array = json_decode($json_data,true);  //将json文本转换为相应的PHP数据结构，通常情况下，json_decode()总是返回一个PHP对象
//echo '<pre>';print_r(count($ufo_array['data']));
//echo '<pre>';print_r($ufo_array);

//删除除了id，content，label，title之外的值
$new_array = array();
$new_ufo_array = array();
$index = 0;
foreach ($ufo_array['data'] as $key => $value){
	//去除空值反馈
	if(array_key_exists("description", $value) && array_key_exists("mobile_app_name", $value)){
		$description = $value['description'];
		$encode = mb_detect_encoding($description, array("ASCII",'UTF-8','GB2312','GBK','BIG5'));
		$description = mb_convert_encoding($description, 'UTF-8', $encode);
		$description = str_replace("\n","",$description);
		$description = str_replace(" ","",$description);
		$description = str_replace("😡","",$description);
		$description = str_replace("😭","",$description);
		$description = str_replace("😊","",$description);
		$description = str_replace("/[^\x{4e00}-\x{9fff}]+/u",'',$description);
		
		$new_array[$index]['id'] = $value['id'];
		$new_array[$index]['content'] = array($description);
		$new_array[$index]['title'] = '';
		$new_array[$index]['label'] = '';
		
		$new_ufo_array[$index] = array();
		$new_ufo_array[$index]['id'] = $value['id'];
		if (array_key_exists("username", $value))
			$new_ufo_array[$index]['username'] = $value['username'];
		else
			$new_ufo_array[$index]['username'] = ' ';
		if (array_key_exists("description", $value))
			$new_ufo_array[$index]['description'] = $value['description'];
		else
			$new_ufo_array[$index]['description'] = ' ';
		if (array_key_exists("product_type", $value)) 	
			$new_ufo_array[$index]['product_type'] = $value['product_type'];
		else
			$new_ufo_array[$index]['product_type'] = ' ';
		if (array_key_exists("mobile_os_version", $value))
			$new_ufo_array[$index]['mobile_os_version'] = $value['mobile_os_version'];
		else
			$new_ufo_array[$index]['mobile_os_version'] = ' ';
		if (array_key_exists("mobile_net_type", $value))
			$new_ufo_array[$index]['mobile_net_type'] = $value['mobile_net_type'];
		else
			$new_ufo_array[$index]['mobile_net_type'] = ' ';
		if (array_key_exists("mobile_os_name", $value))
			$new_ufo_array[$index]['mobile_os_name'] = $value['mobile_os_name'];
		else
			$new_ufo_array[$index]['mobile_os_name'] = ' ';
		if (array_key_exists("mobile_app_name", $value))
			$new_ufo_array[$index]['mobile_app_name'] = $value['mobile_app_name'];
		else
			$new_ufo_array[$index]['mobile_app_name'] = ' ';
		if (array_key_exists("mobile_app_version", $value))
			$new_ufo_array[$index]['mobile_app_version'] = $value['mobile_app_version'];
		else
			$new_ufo_array[$index]['mobile_app_version'] = ' ';
		if (array_key_exists("extend_appid", $value))
			$new_ufo_array[$index]['extend_appid'] = $value['extend_appid'];
		else
			$new_ufo_array[$index]['extend_appid'] = ' ';
		if (array_key_exists("extend_sdk_id", $value))
			$new_ufo_array[$index]['extend_sdk_id'] = $value['extend_sdk_id'];
		else
			$new_ufo_array[$index]['extend_sdk_id'] = ' ';
		if (array_key_exists("extend_pccode", $value))
			$new_ufo_array[$index]['extend_pccode'] = $value['extend_pccode'];
		else
			$new_ufo_array[$index]['extend_pccode'] = ' ';
		if (array_key_exists("mobile_model", $value))
			$new_ufo_array[$index]['mobile_model'] = $value['mobile_model'];
		else
			$new_ufo_array[$index]['mobile_model'] = ' ';
		if (array_key_exists("mobile_screen_size", $value))
			$new_ufo_array[$index]['mobile_screen_size'] = $value['mobile_screen_size'];
		else
			$new_ufo_array[$index]['mobile_screen_size'] = ' ';
		if (array_key_exists("ip", $value))
			$new_ufo_array[$index]['ip'] = $value['ip'];
		else
			$new_ufo_array[$index]['ip'] = ' ';
		if (array_key_exists("submit_time", $value))
			$new_ufo_array[$index]['submit_time'] = $value['submit_time'];
		else
			$new_ufo_array[$index]['submit_time'] = ' ';
		if (array_key_exists("feedback_type", $value))
			$new_ufo_array[$index]['feedback_type'] = $value['feedback_type'];
		else
			$new_ufo_array[$index]['feedback_type'] = ' ';
		$index = $index + 1;
	}
}
//echo '<pre>';print_r($new_array);
$ufo_data = array(
	'product_id'=>'21',
	'data'=>$new_array
);
$json_ufo_data = json_encode($ufo_data);

file_put_contents('data.input', $json_ufo_data);

/**文本分类**/
$url = 'http://cq01-cp01-qa-nlp-06-1.epc.baidu.com:8031';
$uploaddir = getcwd();
$file = $uploaddir.'/data.input';
$fi = new finfo(FILEINFO_MIME_TYPE); 
$mime_type = $fi->file($file); 
// Create a CURLFile object
$cfile = curl_file_create($file, $mime_type, 'input_data');
$post_data = array(
	'id'=>'99',
	'data'=>$cfile//上传文件到服务器
);
$result_data = postData2($url, $post_data);
//echo '<pre>';print_r($result_data);
$array = json_decode($result_data,true);  
//echo '<pre>';print_r($array); 
//echo 'test';
$classify_array = $array['result'][0]['data'][0]['content'];
$new_classify_array = array();
foreach($classify_array as $key => $value){
	$temp = array();
	$temp = explode('	',$value);
	$new_classify_array[$temp[0]] = $temp[1];
}
//echo '<pre>';print_r($new_classify_array);
$title = array();
foreach($new_ufo_array[0] as $key => $value){
	$title[] = $title_cov_arr[$key];
}
//echo '<pre>';print_r($title);

//将分类结果写入初始用户反馈数据
$classify_flag = 0;
$product_type = array();
$id = array();
foreach ($new_ufo_array as $key => $value){
	$new_ufo_array[$key]['product_type'] = $new_classify_array[$value['id']];
	$id[$key] = $value['id'];
	$product_type[$key] = $new_classify_array[$value['id']];
}
//多维排序
array_multisort($product_type, $id, $new_ufo_array);
$outputfilename="ufo_classify_list.xls";
exportExcel($new_ufo_array, $title, $outputfilename, 'UFO-'.date('Ymd ',strtotime('-1 day')));
//数组根据分类分割为多个数据，并且保存到xls
$seg_ufo_array = array();
foreach ($new_ufo_array as $key => $value){
	$seg_ufo_array[$value['product_type']][] = $value;
}

//根据分类数排序
$count_seg_ufo_array = array();
foreach ($seg_ufo_array as $key => $value){
	$count_seg_ufo_array[$key] = count($value);
}
array_multisort($count_seg_ufo_array, SORT_DESC, $count_seg_ufo_array);
echo '<pre>';print_r($count_seg_ufo_array);
//写入txt文档
file_put_contents("seg_ufo_list.txt", $count_seg_ufo_array);
$theFile = fopen ( "seg_ufo_list.txt", "w" ); // 创建读取文件 
if (file_exists ( "seg_ufo_list.txt" )) { // 判断文件是否存在 
	if (is_writable ( "seg_ufo_list.txt" )) { 
		foreach ($count_seg_ufo_array as $key => $value){
			$theStr = $key.' '.$value."\n";
			fwrite ( $theFile, $theStr );
		}
	} 
}
fclose ( $theFile );

foreach ($count_seg_ufo_array as $key => $value){
    $outputfilename = $value.".xls";
	exportExcel($seg_ufo_array[$key], $title, $outputfilename, $key.date('Ymd ',strtotime('-1 day')));
}

//echo 'success!';

/**common function**/
function compare($x, $y){
	if(count($x) > count($y))
		return 1;
	return 0;
}

function postData($url, $data)  {  
    $ch = curl_init();  //初始化，创建一个新cURL资源
    $timeout = 300;  
    curl_setopt($ch, CURLOPT_URL, $url); //设置URL和相应的选项
    curl_setopt($ch, CURLOPT_POST, true);  
    curl_setopt($ch, CURLOPT_POSTFIELDS, $data);  
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);  
    curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, $timeout);  
    $handles = curl_exec($ch);  //抓取URL并把它传递给浏览器
    curl_close($ch);  //关闭cURL资源，并且释放系统资源
    return $handles;  
}  

function postData2($url, $data)  {  
    $ch = curl_init();  //初始化，创建一个新cURL资源
    $timeout = 300;  
    curl_setopt($ch, CURLOPT_URL, $url); //设置URL和相应的选项
    curl_setopt($ch, CURLOPT_POST, true);  
    curl_setopt($ch, CURLOPT_POSTFIELDS, $data);  
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
	curl_setopt($ch, CURLOPT_VERBOSE, 0);
	curl_setopt($ch, CURLOPT_HEADER, false);	
    $handles = curl_exec($ch);  //抓取URL并把它传递给浏览器
    curl_close($ch);  //关闭cURL资源，并且释放系统资源
    return $handles;  
}

function exportExcel($data, $title, $outputfilename, $sheettitle){
	$objPHPExcel = new PHPExcel();
    //设置基本属性
    $objPHPExcel->getProperties()->setCreator("lj")
                                 ->setLastModifiedBy("lj")
                                 ->setTitle("Office 2003 XLSX Test Document")
                                 ->setSubject("Office 2003 XLSX Test Document")
                                 ->setDescription("Test document for Office 2003 XLSX, generated using PHP classes.")
                                 ->setKeywords("office 2003 openxml php")
                                 ->setCategory("Test result file");
	//设置工作薄索引
    $objPHPExcel->setActiveSheetIndex(0);
    //设置工作薄名称
    $objAtvSheet=$objPHPExcel->getActiveSheet();
    //设置宽度自适应
    //$objAtvSheet->getColumnDimensions()->setAutoSize(true);
    $objAtvSheet->getColumnDimension('C')->setWidth(50);
    $objAtvSheet->getColumnDimension('H')->setWidth(20);
    $objAtvSheet->getColumnDimension('L')->setWidth(50);
    $objAtvSheet->setTitle($sheettitle);
    //设置标题
    $objAtvSheet->setCellValue('A1','百度浏览器Android');
    $objAtvSheet->mergeCells('A1:Q1');//合并单元格
    $objAtvSheet->mergeCells('A2:Q2');
    //设置单元格标题内容，表头
	foreach($title as $col => $value){
		$objAtvSheet->setCellValueByColumnAndRow($col, 3, $value);
	}
   
    $count=count($data);
    $RetentionRow=4;                //设置开始的一行
    //得到最后一行
    $endLine=$RetentionRow+$count-1;
    //设置表头背景颜色
    $header_Background=new PHPExcel_Style();
    $header_Background->applyFromArray(
        array(
            'fill'=>array(
                'type'=>PHPExcel_Style_Fill::FILL_SOLID,
                'color'=>array('argb'=>'FF888888')
            ),
            'borders'=>array(
                'right'=>array('style'=>PHPExcel_Style_Border::BORDER_THIN),
                'bottom'=>array('style'=>PHPExcel_Style_Border::BORDER_THIN),
                'top'=>array('style'=>PHPExcel_Style_Border::BORDER_THIN),
                'left'=>array('style'=>PHPExcel_Style_Border::BORDER_THIN)
            )
        )
    );
    $objAtvSheet->setSharedStyle($header_Background,"A3:Q3");
    //设置内容的边框,注意：这里要先设置边框 在才能设置单元格里的字体颜色
    $content_Border=new PHPExcel_Style();
    $content_Border->applyFromArray(
        array(
            'borders'=>array(
                'left'=>array('style'=>PHPExcel_Style_Border::BORDER_THIN),
                'top'=>array('style'=>PHPExcel_Style_Border::BORDER_THIN),
                'right'=>array('style'=>PHPExcel_Style_Border::BORDER_THIN),
                'bottom'=>array('style'=>PHPExcel_Style_Border::BORDER_THIN)
            ),
        )
    );
    $objAtvSheet->setSharedStyle($content_Border,"A4:Q$endLine");
    //开始填充数据到excel
    for($i = 0; $i < $count; $i++){
        $line = $i+$RetentionRow;//开始填充的行数
        $col = 0;
        foreach($data[$i] as $key => $value){
        	$encode = mb_detect_encoding($value, array('ASCII','UTF-8','GB2312','GBK','BIG5'));
        	//echo $encode;
        	//$s = mb_convert_encoding($value,"gb2312",$encode);//从utf-8转换为gb2312
        	$s = iconv($encode, "UTF-8//IGNORE", $value);//从utf-8转换为gb2312
        	$objAtvSheet->setCellValueByColumnAndRow($col, $line, $s);
        	$col = $col + 1;
        }
        //设置隔行换色
        /**if($i % 2 ==1)
        {	
        	$objAtvSheet->getStyle("A$line:Q$line")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
			$objAtvSheet->getStyle("A$line:Q$line")->getFill()->getStartColor()->setARGB('FF808080');
        }**/
       
    }
    //设置自动换行
    $objAtvSheet->getStyle("A3:Q$endLine")->getAlignment()->setWrapText(true);
    //设置 字体加粗，居中
    $objAtvSheet->getStyle('A1:Q1')->getFont()->setBold(true);
    $objAtvSheet->getStyle('A1:Q1')->getFont()->setSize(20);
    $objAtvSheet->getStyle('A1:Q1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
    $objAtvSheet->getStyle('A1:Q1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $objAtvSheet->getStyle('A1:Q1')->getFont()->setName('新宋体');
    $objAtvSheet->getStyle('A1:Q1')->getFont()->getColor()->setRGB('E85656');
    $objAtvSheet->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
   
    //设置内容 居中
    $objAtvSheet->getStyle("A4:Q$endLine")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
    $objAtvSheet->getStyle("A3:Q3")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    //ob_end_clean();//清除缓冲区,避免乱码
    $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_memcache;
    $cacheSettings = array( 'memcacheServer'  => 'localhost',
    		'memcachePort'    => 11211,
    		'cacheTime'       => 600
    );
    PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    $objWriter->save($outputfilename);
}

?>
